/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sisfo.astacala.form;

import java.util.*;
import javax.servlet.http.HttpServletRequest;
import sisfo.astacala.model.ModelUser;
import sisfo.astacala.object.ObjectColumn;
import sisfo.astacala.util.UtilDatabase;
import sisfo.astacala.util.UtilFormExporter;
import sisfo.astacala.util.UtilGeneral;

/**
 *
 * @author arif
 */
public class FormMaster implements FormInterface {

    private static FormMaster formUser;

    public static FormMaster getInstance() {
        if (formUser == null) {
            formUser = new FormMaster();
        }
        return formUser;
    }

    // <editor-fold desc="load grid">
    @Override
    public List<?> getGridData(String queryColumn, String queryData) {
        List resultList = new ArrayList();
        try {

//            String queryColumn = String.format("from ObjectColumn where parent='%s'", query);
//            String queryData = String.format("select * from %s", "tbls_" + query);            

//            System.out.println("=== query column : " + queryColumn);
//            System.out.println("=== query data : " + queryData);

            List<ObjectColumn> objectList = (List<ObjectColumn>) ModelUser.getInstance().searchModel(queryColumn);

            List columnNames = new ArrayList();
            List columnModel = new ArrayList();
            List columnData = new ArrayList();

            UtilDatabase.getInstance().openConnection();
            List tempList = UtilDatabase.getInstance().executeQuery(queryData, new ArrayList());

            // generate column (names & model) grid
            columnNames.add("");
            Map tempAction = new HashMap();
            tempAction.put("name", "act");
            tempAction.put("index", "act");
            tempAction.put("width", "110");
            tempAction.put("search", false);
            tempAction.put("jsonmap", null);
            columnModel.add(tempAction);

            columnNames.add("id");
            Map tempActionId = new HashMap();
            tempActionId.put("name", "id");
            tempActionId.put("index", "id");
            tempActionId.put("hidden", true);
            tempActionId.put("editable", false);
            tempActionId.put("jsonmap", null);
            columnModel.add(tempActionId);

            for (ObjectColumn column : objectList) {
                columnNames.add(column.getName());

                Map tempMap = new HashMap();
                tempMap.put("name", column.getName());
                tempMap.put("index", column.getIndex());
                tempMap.put("editable", true);
                tempMap.put("jsonmap", null);

                if (column.getEditoptions() == null) {
                } else {
                    tempMap.put("edittype", "select");
                    tempMap.put("editoptions", getEditOptions(column.getEditoptionskey(), column.getEditoptions()));
                }

                columnModel.add(tempMap);
            }

            // generate data
            for (Object dataRow : tempList) {
                Map tempMap = new HashMap();

                String rowId = ((Map) dataRow).get("id").toString();
//                String addHref = String.format(" <a href='javascript:addGrid(%s)'>New</a> ", rowId);
//                String editHref = String.format(" <a href='javascript:editGrid(%s)'>Edit</a> ", rowId);
//                String deleteHref = String.format(" <a href='javascript:deleteGrid(%s)'>Delete</a> ", rowId);

                String addHref = String.format(" <a href='javascript:addGrid(jQuery(" + "\"#grid\").jqGrid(), %s)'>New</a> ", rowId);
                String editHref = String.format(" <a href='javascript:editGrid(jQuery(" + "\"#grid\").jqGrid(), %s)'>Edit</a> ", rowId);
                String deleteHref = String.format(" <a href='javascript:deleteGrid(jQuery(" + "\"#grid\").jqGrid(), %s)'>Delete</a> ", rowId);

                tempMap.put("act", addHref + editHref + deleteHref);
                for (Object cn : columnNames) {
                    if (cn.equals("act")) {
                    } else {
                        tempMap.put(cn, ((Map) dataRow).get(cn.toString().toLowerCase()));
                    }
                }
                columnData.add(tempMap);
            }

            resultList.add(columnNames);
            resultList.add(columnModel);
            resultList.add(columnData);
            resultList.add(getKeySearch(objectList));

            UtilDatabase.getInstance().closeConnection();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return resultList;
    }

    public Map getEditOptions(String editOptionsKey, String editOptions) {
        String editoptionsQuery = String.format("select %s from %s", editOptionsKey, editOptions);
        Map result = new HashMap();
        UtilDatabase.getInstance().openConnection();

        List editOptionsList = UtilDatabase.getInstance().executeQuery(editoptionsQuery, new ArrayList());
        StringBuilder stringBuilder = new StringBuilder();
        for (int i = 0; i < editOptionsList.size(); i++) {
            String key = (String) ((Map) editOptionsList.get(i)).get(editOptionsKey);
            stringBuilder.append(key).append(":").append(key);

            if ((i + 1) < editOptionsList.size()) {
                stringBuilder.append(";");
            }
        }

        UtilDatabase.getInstance().closeConnection();

        result.put("value", stringBuilder.toString());
        return result;
    }

    public String getKeySearch(List<ObjectColumn> columns) {
        StringBuilder keySearch = new StringBuilder();
        keySearch.append("<select>");
        keySearch.append("<option>-</option>");
        for (ObjectColumn column : columns) {
            keySearch.append("<option>").append(column.getName()).append("</option>");
        }
        keySearch.append("</select>");
        return keySearch.toString();
    }
// </editor-fold>

    // <editor-fold desc="add grid">
    public boolean addGridData(HttpServletRequest request, String tableSource) {
        boolean result = false;
        try {
            Enumeration parameters = request.getParameterNames();

            List parametersName = new ArrayList();
            List parametersValue = new ArrayList();
            StringBuilder queryBuilder = new StringBuilder();

            queryBuilder.append("INSERT INTO ").append(tableSource).append(" (");

            while (parameters.hasMoreElements()) {
                String parameterName = parameters.nextElement().toString();
                String parameterValue = request.getParameter(parameterName);

                if ((parameterName.equals("oper")) || (parameterName.equals("id"))) {
                } else {
                    if (parameterValue.equals("")) {
                    } else {
                        parametersName.add(parameterName);
                        parametersValue.add(parameterValue);
                    }
                }
            }

            for (int i = 0; i < parametersName.size(); i++) {
                if ((i + 1) != parametersName.size()) {
                    queryBuilder.append(parametersName.get(i)).append(",");
                } else {
                    queryBuilder.append(parametersName.get(i));
                }
            }

            queryBuilder.append(") VALUES (");

            for (int i = 0; i < parametersValue.size(); i++) {
                if ((i + 1) != parametersValue.size()) {
                    queryBuilder.append("?,");
                } else {
                    queryBuilder.append("?)");
                }
            }

//            System.out.println("query builder : " + queryBuilder.toString().replaceAll(",W", " W"));
//            System.out.println("query param : " + parametersValue);

            UtilDatabase.getInstance().openConnection();
            UtilDatabase.getInstance().executeUpdate(queryBuilder.toString(), parametersValue);
            UtilDatabase.getInstance().closeConnection();

            result = true;
        } catch (Exception ex) {
            ex.printStackTrace();
        }

        return result;
    }
    // </editor-fold>

    // <editor-fold desc="edit grid">
    public Map editGridData(HttpServletRequest request, String tableSource) {
        boolean result = false;
        Map editData = new HashMap();
        try {
            Enumeration parameters = request.getParameterNames();

            List parametersName = new ArrayList();
            List parametersValue = new ArrayList();
            StringBuilder queryBuilder = new StringBuilder();
            String id = "";
            queryBuilder.append("UPDATE ").append(tableSource).append(" SET ");

            while (parameters.hasMoreElements()) {
                String parameterName = parameters.nextElement().toString();
                String parameterValue = request.getParameter(parameterName);

                if (parameterName.equals("id")) {
                    id = parameterValue;
                } else if (parameterName.equals("oper")) {
                } else {
                    if (parameterValue.equals("")) {
                    } else {
                        editData.put(parameterName, parameterValue);
                        parametersName.add(parameterName);
                        parametersValue.add(parameterValue);
                    }
                }
            }

            for (int i = 0; i < parametersName.size(); i++) {
                if ((i + 1) != parametersName.size()) {
                    queryBuilder.append(parametersName.get(i)).append("=?,");
                } else {
                    queryBuilder.append(parametersName.get(i)).append("=? ");
                }
            }

            queryBuilder.append("WHERE id='").append(id).append("'");

//            System.out.println("query builder : " + queryBuilder.toString().replaceAll(",W", " W"));
//            System.out.println("query param : " + parametersValue);

//            UtilDatabase.getInstance().openConnection();
//            UtilDatabase.getInstance().executeUpdate(queryBuilder.toString(), parametersValue);
//            UtilDatabase.getInstance().closeConnection();

            result = true;
        } catch (Exception ex) {
            ex.printStackTrace();
        }

        return editData;
    }

//    public String getDivisions(String query) {
//        StringBuilder result = new StringBuilder();
//        try {
//            Session session = UtilHibernate.getSessionFactory().openSession();
//            String optionFormat = "<option value='%s'>%s</option> ";
//
//            session.getTransaction();
//            List<ObjectDivision> divisions = session.createQuery(query).list();
//
//            result.append("<select>");
//            for (ObjectDivision division : divisions) {
//                result.append(String.format(optionFormat, division.getId(), division.getDivision()));
//            }
//            result.append("</select>");
//
//            session.close();
//        } catch (Exception ex) {
//            ex.printStackTrace();
//        }
//        return result.toString();
//    }
// </editor-fold>
    
    // <editor-fold desc="delete grid">
    public boolean deleteGridData(String tableSource, String dataId) {
        boolean result = false;
        try {
            String deleteQuery = String.format("Delete From %s WHERE id=%s", tableSource, dataId);

            UtilDatabase.getInstance().openConnection();
            UtilDatabase.getInstance().executeUpdate(deleteQuery, new ArrayList());
            UtilDatabase.getInstance().closeConnection();
            result = true;
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return result;
    }
// </editor-fold>

    //<editor-fold desc="export grid">
    public void exportGridData(HttpServletRequest request, String key) {
        try {
            String queryColumn = String.format("SELECT * FROM tbls_column WHERE parent='%s'", key);
            StringBuilder queryData = new StringBuilder();
            List<String> columnName = new ArrayList<String>();

            UtilDatabase.getInstance().openConnection();

            List<Map> columns = UtilDatabase.getInstance().executeQuery(queryColumn, new ArrayList());
            queryData.append("SELECT ");
            for (int i = 0; i < columns.size(); i++) {
                queryData.append(columns.get(i).get("columnname"));
                if ((i + 1) != columns.size()) {
                    queryData.append(", ");
                }
                columnName.add(columns.get(i).get("columnname").toString());
            }
            queryData.append(" FROM ").append(String.format("tbls_%s", key));
            if (request.getParameter("key").equals("-")) {
            } else {
                queryData.append(" WHERE ").append(request.getParameter("key").toLowerCase()).append(String.format("='%s'", request.getParameter("keyvalue")));
            }
            
            List<Map> data = UtilDatabase.getInstance().executeQuery(queryData.toString(), new ArrayList());
            
            UtilFormExporter formExporter = new UtilFormExporter();
            formExporter.setFileName(UtilGeneral.exportCsvPath + String.format("DATA_%s_%s.csv", key, UtilGeneral.getInstance().getCurrentDate("yyyyMMddHHmmss")));
            formExporter.setExportType(request.getParameter("exportType"));
            formExporter.setColumnName(columnName);
            formExporter.setData(data);
            formExporter.run();
//            formExporter.exportToCSV(columnName, data);
            
            UtilDatabase.getInstance().closeConnection();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
    //</editor-fold>
}
